package com.lagou.demo.dao;

import com.lagou.demo.pojo.Page;
import com.lagou.demo.pojo.Student;
import com.lagou.demo.pojo.User;
import com.lagou.demo.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 实现对数据库进行增加方法
 */
public class StudentDaoImp implements StudentDao {
    @Override
    public int StudentAdd(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtil.getConnection();
            // 2.准备sql语句
            String sql = "insert into t_student values (null,?,?,?,?,?,?)";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setDate(3, new java.sql.Date(student.getBirthday().getTime()));
            preparedStatement.setString(4, student.getEmail());
            preparedStatement.setString(5, student.getComments());
            preparedStatement.setInt(6, student.getClassNumber());
            i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("添加成功！");
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtil.close(connection, preparedStatement);
        }
        return 0; // 表示查找失败
    }

    /**
     * 实现对数据库进行删除方法
     */
    @Override
    public int StudentDelete(String[] id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取Connection连接对象
            connection = DruidUtil.getConnection();
            // 2.准备插入数据的SQL语句
            // 2.1 拼接一下所有的id
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < id.length; i++) {
                if (i == id.length - 1) {
                    sb.append(id[i]);
                } else {
                    sb.append(id[i]).append(",");
                }
            }
            // System.out.println("sb = " + sb);
            // 2.2 拼接sql语句，in是指在（）里面的数字进行删除，可以达到同时删除多个数据的效果
            String sql = "delete from t_student where id in(" + sb + ")";//
            // 3.获取PreparedStatement并执行SQL语句
            preparedStatement = connection.prepareStatement(sql);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement);
        }
        return 0; // 表示删除失败
    }

    /**
     * 实现对数据库进行修改方法
     */
    @Override
    public int StudentUpdate(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtil.getConnection();
            // 2.准备sql语句
            String sql = "update t_student set `name` = ?,sex=?,birthday=?,email=?,comments=?,className=? where id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setDate(3, new java.sql.Date(student.getBirthday().getTime()));
            preparedStatement.setString(4, student.getEmail());
            preparedStatement.setString(5, student.getComments());
            preparedStatement.setInt(6,student.getClassNumber());
            preparedStatement.setInt(7, student.getId());
            i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("修改成功！");
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtil.close(connection, preparedStatement);
        }
        return 0; // 表示查找失败
    }

    /**
     * 实现对数据库进行查询方法
     */
    @Override
    public Student StudentFind(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet i = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtil.getConnection();
            // 2.准备sql语句
            String sql = "select * from t_student where id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            i = preparedStatement.executeQuery();
            while (i.next()) {
                Student student = new Student();
                student.setId(i.getInt("id"));
                student.setName(i.getString("name"));
                student.setBirthday(new java.sql.Date(i.getDate("birthday").getTime()));
                student.setEmail(i.getString("email"));
                student.setComments(i.getString("comments"));
                student.setClassNumber(i.getInt("classNumber"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtil.close(connection, preparedStatement, i);
        }
        return null; // 表示查找失败
    }

    /**
     * 实现对数据库进行查询所有的学生信息
     */
    @Override
    public List<Student> StudentSearch(Page page) {
        List<Student> studentList = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取Connection连接对象
            connection = DruidUtil.getConnection();
            // 2.使用StringBuilder类型的对象来描述SQL语句的目的在于后续可以发生改变
            StringBuilder stringBuilder = new StringBuilder("select * from t_student");
            // 3.当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (page != null) {
                stringBuilder.append(" limit " + page.getStart() + "," + page.getRow());
            }
            // 4.获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            // 5.将结果集中的数据取出并放到List集合中返回
            studentList = new ArrayList<>();
            while (resultSet.next()) {
                Student student = new Student(resultSet.getInt("id"), resultSet.getString("name"),
                        resultSet.getString("sex"), new java.util.Date(resultSet.getDate("birthday").getTime()),
                        resultSet.getString("email"), resultSet.getString("comments"),resultSet.getInt("classNumber"));
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return studentList;
    }
}
